package database.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import database.pojo.UserInfo;
import manager.player.basic.type.BasicPlayerInfo;
import manager.rank.type.PlayerRankInfo;
import manager.rank.type.RankType;


public class UserInfoDao {
	
	public UserInfo insert(UserInfo userInfo, Connection conn) throws SQLException{
		String sqlInsert = "insert into UserInfo(WechatOpenid, NickName, Sex, Country, Province, City, Language, HeadImgUrl, SubscribeTime,AvatarId,LastLoginTime,IsOnline,LoginMachine) values( ? , ? , ? , ? , ? , ? , ? , ? , ? ,?,?,?,?)";
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sqlInsert);//相当于python操作数据库的游标
			System.out.println("用户的昵称:");
			System.out.println(userInfo.getNickName());
			
			pstmt.setString(1, userInfo.getWechatOpenid());
			pstmt.setString(2, userInfo.getNickName());
			pstmt.setInt(3, userInfo.getSex());
			pstmt.setString(4, userInfo.getCountry());
			pstmt.setString(5, userInfo.getProvince());
			pstmt.setString(6, userInfo.getCity());
			pstmt.setString(7, userInfo.getLanguage());
			pstmt.setString(8, userInfo.getHeadImgUrl());
			pstmt.setLong(9, userInfo.getSubscribeTime());
			pstmt.setInt(10, userInfo.getAvatarId());
			pstmt.setLong(11, userInfo.getLastLoginTime());
			pstmt.setInt(12, userInfo.getIsOnline());
			pstmt.setInt(13, userInfo.getLoginMachine());
			//emoji表情4个字节必须要用utf8mb4
			pstmt.executeQuery("SET NAMES utf8mb4");
			pstmt.executeUpdate();
		}
		finally{
			if(pstmt != null){
				pstmt.close();
			}
		}
		return userInfo;
		
	}
	
	public UserInfo update(UserInfo userInfo, Connection conn) throws SQLException{
		String sqlUpdate = "update UserInfo set NickName = ? , Sex = ? , Country = ? , Province = ? , City = ? , Language = ? , HeadImgUrl = ? , SubscribeTime = ?,AvatarId = ?,LastLoginTime = ?,IsOnline = ?,LoginMachine = ? where WechatOpenid = ? ";
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setString(1, userInfo.getNickName());
			System.out.println("nick name is: " + userInfo.getNickName());
			pstmt.setInt(2, userInfo.getSex());
			pstmt.setString(3, userInfo.getCountry());
			pstmt.setString(4, userInfo.getProvince());
			pstmt.setString(5, userInfo.getCity());
			pstmt.setString(6, userInfo.getLanguage());
			pstmt.setString(7, userInfo.getHeadImgUrl());
			pstmt.setLong(8, userInfo.getSubscribeTime());
			pstmt.setInt(9, userInfo.getAvatarId());
			//System.out.println("nick name is: " + userInfo.getAvatarId());
			pstmt.setLong(10, userInfo.getLastLoginTime());
			pstmt.setInt(11, userInfo.getIsOnline());
			pstmt.setInt(12, userInfo.getLoginMachine());
			pstmt.setString(13, userInfo.getWechatOpenid());
			//emoji表情4个字节必须要用utf8mb4
			pstmt.executeQuery("SET NAMES utf8mb4");
			pstmt.executeUpdate();
		}catch (Exception e) {
			System.out.println(" update Exception" + e);
		}
		finally{
			if(pstmt != null){
				pstmt.close();
			}
		}
		return userInfo;
	}
	
	public UserInfo updateExceptLastLoginTime(UserInfo userInfo, Connection conn) throws SQLException{
		String sqlUpdate = "update UserInfo set IsOnline = ?,LoginMachine = ? ,LastLoginTime =?, LoginTimes=LoginTimes+1, EndTime=? where WechatOpenid = ? ";
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setInt(1, userInfo.getIsOnline());
			pstmt.setInt(2, userInfo.getLoginMachine());
			pstmt.setLong(3, userInfo.getLastLoginTime());
			pstmt.setLong(4, userInfo.getEndTime());
			pstmt.setString(5, userInfo.getWechatOpenid());
			
			pstmt.executeUpdate();
		}finally{
			if(pstmt != null){
				pstmt.close();
			}
		}
		return userInfo;
	}
	
	public boolean isExist(UserInfo userInfo, Connection conn) throws SQLException{
		String sqlSelect = "select * from UserInfo where WechatOpenid = ? ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1, userInfo.getWechatOpenid());
			rs = pstmt.executeQuery();
			if(rs.next())

				if(rs.getString("WechatOpenid") != null)
					return true;
				else {
					return false;
				}
			else {
				 return false;
			}
		}
		finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
		}
	}	
	
	public int updateAvataridByOpenId(int avatarId ,String openId,Connection conn) throws SQLException{
		String sqlUpdate = "update UserInfo set AvatarId = ? where WechatOpenid = ?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setInt(1, avatarId);
			pstmt.setString(2, openId);
			pstmt.executeUpdate();
			
		} finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
		return avatarId;
	}
	
	
	public UserInfo selectByOpenId(String openId, Connection conn) throws SQLException {
		String sqlSelect = "select * from UserInfo where WechatOpenid = ? ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		UserInfo userInfo = null;
		//System.out.println("get open id " + openId);
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1, openId);
			rs = pstmt.executeQuery();
			if(rs.next()){
				//System.out.println("find a result");
				if(rs.getString("WechatOpenid") != null){
					userInfo = new UserInfo();
					userInfo.setWechatOpenid(rs.getString("WechatOpenid"));
					userInfo.setCity(rs.getString("City"));
					userInfo.setCountry(rs.getString("Country"));
					userInfo.setHeadImgUrl(rs.getString("HeadImgUrl"));
					userInfo.setLanguage(rs.getString("Language"));
					userInfo.setNickName(rs.getString("NickName"));
					userInfo.setProvince(rs.getString("Province"));
					userInfo.setSex(rs.getInt("Sex"));
					userInfo.setSubscribeTime(rs.getLong("SubscribeTime"));
					userInfo.setUid(rs.getInt("UID"));
					userInfo.setAvatarId(rs.getInt("AvatarId"));
					userInfo.setIsOnline(rs.getInt("IsOnline"));
					userInfo.setLastLoginTime(rs.getLong("LastLoginTime"));
					userInfo.setLoginMachine(rs.getInt("LoginMachine"));
					userInfo.setLvl(rs.getInt("Lvl"));
					userInfo.setEndTime(rs.getLong("EndTime"));
					userInfo.setkCoin(rs.getInt("kCoin"));
					userInfo.setVipGrade(rs.getInt("vipGrade"));
					userInfo.setAll_recharge(rs.getDouble("all_recharge"));
					userInfo.setAll_consume(rs.getDouble("all_consume"));
					userInfo.setGMPlayer(rs.getInt("GMPlayer"));
					userInfo.setUid(rs.getInt("UID"));
				}
			}
		}finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
		}
		return userInfo;
	}

	public int getUidByOpenId(Connection conn, String openId) throws SQLException {
		String sqlSelect = "select uid from UserInfo where WechatOpenid = ? ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int uid=0;
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1, openId);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				uid = rs.getInt("UID");
			}
		}
		finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
		}
		return uid;
	}

	public int getKCodeByOpenId(Connection conn, String openId) throws SQLException {
		String sqlSelect = "select kCoin from UserInfo where WechatOpenid = ? ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int kCoin = 0;
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1, openId);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				kCoin =rs.getInt("kCoin");
			}
		}
		finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
			
		}
		return kCoin;
	}

	public void updateKCoin(Connection conn, String openId, int getkCoin) throws SQLException {
		String sqlUpdate = "update UserInfo set kCoin = ? where WechatOpenid = ?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setInt(1, getkCoin);
			pstmt.setString(2, openId);
			pstmt.executeUpdate();
			
		} finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
		
	}

	public void updateEndtime(Connection conn, String openId, long endTime, int minites) throws SQLException {
		String sqlUpdate = "update UserInfo set EndTime = ?,ActiveTime=ActiveTime+? where WechatOpenid = ?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setLong(1, endTime);
			pstmt.setInt(2, minites);
			pstmt.setString(3, openId);
			pstmt.executeUpdate();
			
		} finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
		
	}

	public void updateUserLogout(Connection conn, UserInfo userInfo) throws SQLException {
		String sqlUpdate = "update UserInfo set LoginMachine = ? ,IsOnline=? where WechatOpenid = ?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setLong(1, userInfo.getLoginMachine());
			pstmt.setInt(2, userInfo.getIsOnline());
			pstmt.setString(3, userInfo.getWechatOpenid());
			pstmt.executeUpdate();
			
		} finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
		
	}

	public void updateVipGrade(Connection conn, int vipGrade, String openId) throws SQLException {
		String sqlUpdate = "update UserInfo set vipGrade =  ? where WechatOpenid = ?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setInt(1, vipGrade);
			pstmt.setString(2, openId);
			pstmt.executeUpdate();
			
		} finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
		
	}

	public void updateAllRecharge(Connection conn, double afterAllRecharge, String openId) throws SQLException {
		String sqlUpdate = "update UserInfo set all_recharge =  ? where WechatOpenid = ?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setDouble(1, afterAllRecharge);
			pstmt.setString(2, openId);
			pstmt.executeUpdate();
			
		} finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
	}

	public void updateAllConsume(Connection conn, double afterAllConsume, String openId) throws SQLException {
		String sqlUpdate = "update UserInfo set all_consume =  ? where WechatOpenid = ?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setDouble(1, afterAllConsume);
			pstmt.setString(2, openId);
			pstmt.executeUpdate();
			
		} finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
	}

	public void updateEndtimeAndAllConsume(Connection conn, long endTime, double afterAllConsume, String openId, int minites) throws SQLException {
		String sqlUpdate = "update UserInfo set all_consume =  ?,EndTime = ?,ActiveTime=ActiveTime+? where WechatOpenid = ?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setDouble(1, afterAllConsume);
			pstmt.setLong(2, endTime);
			pstmt.setInt(3, minites);
			pstmt.setString(4, openId);
			pstmt.executeUpdate();
			
		} finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
		
	}

	
	public void updateWinOrDefeat(Connection conn, String winer, String defeater) throws SQLException {
		String sqlUpdateWiner = "update UserInfo set WinTimes =WinTimes+1 where WechatOpenid=?";
		String sqlUpdateDefeater = "update UserInfo set DefeatTimes =DefeatTimes+1 where WechatOpenid=?";
		PreparedStatement pstmt = null;
		try {
			pstmt =conn.prepareStatement(sqlUpdateWiner);
			pstmt.setString(1, winer);
			pstmt.executeUpdate();
			pstmt= conn.prepareStatement(sqlUpdateDefeater);
			pstmt.setString(1, defeater);
			pstmt.executeUpdate();
		} finally {
			if(pstmt != null){
				pstmt.close();
			}
		}
		
	}

	//将排行信息添加到list里面，如果这个openid在这个排行榜里面则返回排名，否则返回0
	public int getTotalRank(Connection conn, int rankRange, int number, List<PlayerRankInfo> playerRankInfoList, String openId, int rankType) throws SQLException {
		
		String sqlSelect ="";
		String rankValue="";
		if(rankType==RankType.DailyChargeRank) {
			rankValue="all_recharge";
			if(rankRange==0) {
				sqlSelect="select AvatarId,HeadImgURL,Lvl,NickName,WechatOpenid,Sex,vipGrade,all_recharge from UserInfo  order by all_recharge desc limit ?";
			}else {
				//暂时只处理全部机器的充值榜
				sqlSelect="select AvatarId,HeadImgURL,Lvl,NickName,WechatOpenid,Sex,vipGrade,all_recharge from UserInfo order by all_recharge desc limit ?";
			}
		}else if (rankType==RankType.WinRank) {
			rankValue="WinTimes";
			if(rankRange==0) {
				sqlSelect="select AvatarId,HeadImgURL,Lvl,NickName,WechatOpenid,Sex,vipGrade,WinTimes from UserInfo order by WinTimes desc limit ?";
			}else {
				//暂时只处理全部机器的充值榜
				sqlSelect="select AvatarId,HeadImgURL,Lvl,NickName,WechatOpenid,Sex,vipGrade,WinTimes from UserInfo order by WinTimes desc limit ?";
			}
		}else {
			return 0;
		}
		PlayerRankInfo pInfo =null;
		BasicPlayerInfo bPinfo = null;
		PreparedStatement pstmt = null;
		int rank=0;
		ResultSet rs = null;
		int resultRank=0;
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setInt(1, number);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				rank++;
				pInfo = new PlayerRankInfo();
				bPinfo = new BasicPlayerInfo();
				bPinfo.setAvatarId(rs.getInt("AvatarId"));
				bPinfo.setHeadImgURL(rs.getString("HeadImgURL"));
				bPinfo.setLvl(rs.getInt("Lvl"));
				bPinfo.setNickName(rs.getString("NickName"));
				bPinfo.setOpenId(rs.getString("WechatOpenid"));
				if(bPinfo.getOpenId().equals(openId))
					resultRank=rank;
				bPinfo.setVipGrade(rs.getInt("vipGrade"));
				pInfo.setPlayerInfo(bPinfo);
				pInfo.setRank(rank);
				pInfo.setRankValue(rs.getInt(rankValue));
				playerRankInfoList.add(pInfo);
			}
		}
		finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
			
		}
		return resultRank;
	}

	public PlayerRankInfo getTotal(Connection conn, int rankRange, String openId, int rankType) throws SQLException {
		String sqlSelect ="";
		String rankValue="";
		if(rankType==RankType.DailyChargeRank) {
			rankValue="all_recharge";
			if(rankRange==0) {
				sqlSelect="select AvatarId,HeadImgURL,Lvl,NickName,Sex,vipGrade ,all_recharge from UserInfo where WechatOpenid=?";
			}else {
				//暂时只处理全部机器的充值榜
				sqlSelect="select AvatarId,HeadImgURL,Lvl,NickName,Sex,vipGrade,all_recharge from UserInfo where WechatOpenid=?";
			}
		}else if (rankType==RankType.WinRank) {
			rankValue="WinTimes";
			if(rankRange==0) {
				sqlSelect="select AvatarId,HeadImgURL,Lvl,NickName,Sex,vipGrade ,WinTimes from UserInfo where WechatOpenid=?";
			}else {
				//暂时只处理全部机器的充值榜
				sqlSelect="select AvatarId,HeadImgURL,Lvl,NickName,Sex,vipGrade,WinTimes from UserInfo where WechatOpenid=?";
			}
		}
		
		PlayerRankInfo pInfo =null;
		BasicPlayerInfo bPinfo = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1, openId);;
			rs = pstmt.executeQuery();
			if(rs.next()) {
				pInfo = new PlayerRankInfo();
				bPinfo = new BasicPlayerInfo();
				bPinfo.setAvatarId(rs.getInt("AvatarId"));
				bPinfo.setHeadImgURL(rs.getString("HeadImgURL"));
				bPinfo.setLvl(rs.getInt("Lvl"));
				bPinfo.setNickName(rs.getString("NickName"));
				bPinfo.setOpenId(openId);
				bPinfo.setVipGrade(rs.getInt("vipGrade"));
				pInfo.setPlayerInfo(bPinfo);
				pInfo.setRank(0);//不在榜上设置位0
				pInfo.setRankValue(rs.getInt(rankValue));
			}
		}
		finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
			
		}
		return pInfo;
	}

	public int selectUid(Connection conn, String wechatOpenid) throws SQLException {
		String sqlSelect = "select UID from UserInfo where WechatOpenid = ? ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int uid = 0;
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1, wechatOpenid);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				uid =rs.getInt("UID");
			}
		}
		finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
			return uid;
		}
		
	}


}
